﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using DMS.Base;
using DMS.BaseClass;
using DMS.Business.Modules.Organization;
using DMS.Business.Utility;
using DMS.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace DMS.Business.Modules.ArchivesManagement
{
    public class BaseInfoTempService
    {
        /// <summary>
        /// 查询基本信息
        /// </summary>
        /// <param name="actor">参与者，如果为空则不过滤权限</param>
        /// <param name="condition">条件</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页条数，如果小于等于0，则返回所有</param>
        /// <param name="total">总数</param>
        /// <returns></returns>
        public static List<AM_BaseInfoTemp> GetBaseInfoTemp(string condition, int pageIndex, int pageSize,out int total)
        {
            using (var db = new DMSEntities())
            {
                int skinCount = (pageIndex - 1)*pageSize;

                var q = db.AM_BaseInfoTemp
                    .Where("it.IsRemoved=false " + condition)
                    .OrderBy(p => p.AlarmLevel);

                total = q.Count();

                if (skinCount > total && pageSize > 0)
                {
                    skinCount = total - (total%pageSize);
                }

                List<AM_BaseInfoTemp> list;
                if (pageSize > 0)
                    list = q.Skip(skinCount).Take(pageSize).ToList();
                else
                    list = q.ToList();

                return list;
            }
        }

        public static void UpdateBaseInfoTemp(string infoId,string property,string value)
        {
            using (var db = new DMSEntities())
            {
                var tBaseInfoTemp = db.AM_BaseInfoTemp.FirstOrDefault(p => p.BaseInfoID == infoId);
                string batchId = "";
                if (tBaseInfoTemp != null) //更新
                {
                    batchId = tBaseInfoTemp.BatchID;
                    switch (property)
                    {
                        case "InsuranceCode":
                            tBaseInfoTemp.InsuranceCode = value;
                            break;
                        case "UnitName":
                            tBaseInfoTemp.UnitName = value;
                            break;
                        case "TaxCode":
                            tBaseInfoTemp.TaxCode = value;
                            break;
                    }
                }
                db.SaveChanges();
                CalculateAlarmLevel(batchId);
            }
        }

        public static void RemoveBaseInfoTemp(string baseInfoId)
        {
            using (var db = new DMSEntities())
            {
                var baseInfo = db.AM_BaseInfoTemp.Where(p => p.BaseInfoID == baseInfoId).FirstOrDefault();
                if (baseInfo != null)
                {
                    baseInfo.IsRemoved = true;
                    db.SaveChanges();
                    CalculateAlarmLevel(baseInfo.BatchID);
                }
            }
        }

        public static void RemoveByBatchId(string batchId)
        {
            using (var db = new DMSEntities())
            {
                var baseInfoList = db.AM_BaseInfoTemp.Where(p => p.BatchID == batchId).ToList();
                foreach (var baseInfo in baseInfoList)
                {
                    baseInfo.IsRemoved = true;
                }
                db.SaveChanges();
                CalculateAlarmLevel(batchId);
            }
        }

        /// <summary>
        /// 处理上传的基本信息模板
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="formType"></param>
        /// <returns></returns>
        public static string ProcessBaseInfosExcel(string filePath,string formType)
        {
            //User user = SystemContext.Current.LoginAuthUser as User;
            const string sheetName = "Sheet1";
            var ds = ClientHelper.Instance.ExcelToDS(filePath, sheetName);

            //异常信息
            string errorMessage = "";
            string succeedMessage = "已导入到临时空间，请在确认页面确认并提交。";
            string batchId = "";

            switch (formType)
            {
               case "DW":
                    errorMessage = ProcessBaseInfo(ds, BaseInfoType.DW,out batchId);
                    break;
                case "GTH":
                    errorMessage = ProcessBaseInfo(ds, BaseInfoType.GTH,out batchId);
                    break;
                case "GR":
                    errorMessage = ProcessBaseInfo(ds, BaseInfoType.GR,out batchId);
                    break; 
                default:
                    errorMessage = "请从正确的基础信息列表中导入数据。";
                    break;
            }

            return string.IsNullOrEmpty(errorMessage) ? succeedMessage + ";" + batchId : errorMessage;
        }

        const int SkipRow = 0; //表头跳过行数，模板中有1行表头
        private const string BaseInfoSql = @"SELECT abit.BaseInfoID,
								                       abit.[Type],
								                       abit.InsuranceCode,
								                       abit.UnitName,
								                       abit.TaxCode
							                    FROM   AM_BaseInfoTemp abit where batchId = '{0}'";

        protected static string ProcessBaseInfo(DataSet ds ,BaseInfoType baseInfoType,out string batchId)
        {
            Department currentBigDept = SystemContext.Current.LoginUser.BigDept;
            string errorMessage = "";
            //构造AM_BaseInfoTemp对象，构造有误，不保存，构造无误，放入list中
            var infoList = new List<AM_BaseInfoTemp>();
            batchId = Guid.NewGuid().ToString();

            for (var i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                try
                {
                    var row = ds.Tables[0].Rows[i];

                    var items = row.ItemArray;

                    if (i < SkipRow)
                    {
                        continue;
                    }

                    #region 必填字段检查

                    int requireCount = 6;
                    if(baseInfoType==BaseInfoType.GR)
                    {
                        requireCount = 7;
                    }

                    bool breakCurrentRecord = false;
                    for (int k = 0; k < requireCount; k++)
                    {
                        if (string.IsNullOrEmpty(Convert.ToString(items[k]).Trim()))
                        {

                            errorMessage += "第" + (i+2) + "行数据存在错误：必填字段不可为空，请核查。<br/>";
                            breakCurrentRecord = true;
                            break;
                        }
                    }
                    if (breakCurrentRecord) //不再进行当前记录检查
                    {
                        continue;
                    }

                    #endregion

                    #region 值处理

                    var baseInfoTemp = new AM_BaseInfoTemp();

                    string boxCode="";
                    string boxPosition="";
                    string insuranceCode="";
                    string unitName="";

                    string payerName="";
                    string identityCardNo="";

                    string taxCode="";
                    string payUnitType="";
                    string contact="";
                    string mobile="";
                    string phone="";
                    string admin="";
                    string remark="";
                    string registDateStr="";
                    string insuranceCountStr = "1";
                    string insuranceTypeDateStr="";

                    //字段去空格处理
                    boxCode         = Convert.ToString(items[0]).Trim(); //档案盒编号
                    boxPosition     = Convert.ToString(items[1]).Trim(); //档案盒所在位置
                    insuranceCode    = Convert.ToString(items[2]).Trim(); //单位社保号
                    if(baseInfoType==BaseInfoType.GR)
                    {
                        payerName= Convert.ToString(items[3]).Trim(); //缴费人姓名
                        identityCardNo=Convert.ToString(items[4]).Trim(); //身份证号码
                        taxCode         = Convert.ToString(items[5]).Trim(); //纳税编码
                        payUnitType       = Convert.ToString(items[6]).Trim(); //缴费单位类型
                        mobile         = Convert.ToString(items[7]).Trim(); //联系电话
                        registDateStr= Convert.ToString(items[8]).Trim();
                        insuranceTypeDateStr= Convert.ToString(items[9]).Trim();
                        admin           = Convert.ToString(items[10]).Trim(); //专管员
                        remark         = Convert.ToString(items[11]).Trim(); //备注
                    }else
                    {
                        unitName       = Convert.ToString(items[3]).Trim(); //单位名称
                        taxCode         = Convert.ToString(items[4]).Trim(); //纳税编码
                        payUnitType       = Convert.ToString(items[5]).Trim(); //缴费单位类型
                        contact        = Convert.ToString(items[6]).Trim(); //联系人
                        mobile         = Convert.ToString(items[7]).Trim(); //联系电话（手机）
                        phone          = Convert.ToString(items[8]).Trim(); //联系电话（座机）
                        registDateStr= Convert.ToString(items[9]).Trim();
                        insuranceCountStr= Convert.ToString(items[10]).Trim();
                        insuranceTypeDateStr= Convert.ToString(items[11]).Trim();
                        admin           = Convert.ToString(items[12]).Trim(); //专管员
                        remark         = Convert.ToString(items[13]).Trim(); //备注
                    }
                    
                    //检查日期
                    DateTime registDate;
                    bool registDateParseSucceed = DateTime.TryParse(registDateStr, out registDate);
                    if (!registDateParseSucceed)
                    {
                        errorMessage += "第" + (i+2) + "行数据存在错误：社保登记日期格式有误（必须为“20XX-XX-XX”格式），请核查。<br/>";
                        continue;
                    }
                    //检查参保人数
                    int insuranceCount;
                    bool insuranceCountParseSucceed = Int32.TryParse(insuranceCountStr, out insuranceCount);
                    if (!insuranceCountParseSucceed)
                    {
                        errorMessage += "第" + (i+2) + "行数据存在错误：参保人数必须为数字，请核查。<br/>";
                        continue;
                    }
                    //检查参保类型，并设置到对象
                    var insuranceTypeStrings = insuranceTypeDateStr; //参保险种
                    bool insuranceTypeParseSucceed = GetInsuranceTypes(baseInfoTemp, insuranceTypeStrings);
                    if (!insuranceTypeParseSucceed)
                    {
                        errorMessage += "第" + (i+2) + "行数据存在错误：参保类型有误，请核查。<br/>";
                        continue;
                    }

                    #endregion

                    #region 值复制

                    baseInfoTemp.ID = Guid.NewGuid().ToString();
                    baseInfoTemp.BatchID = batchId;
                    baseInfoTemp.BaseInfoID = Guid.NewGuid().ToString();
                    baseInfoTemp.Type = baseInfoType.ToString();
                    baseInfoTemp.IsRemoved = false;

                    baseInfoTemp.BoxCode = boxCode;
                    baseInfoTemp.BoxPosition = boxPosition;
                    baseInfoTemp.InsuranceCode = insuranceCode;
                    baseInfoTemp.UnitName = unitName;
                    baseInfoTemp.TaxCode = taxCode;
                    baseInfoTemp.PayUnitType = payUnitType;
                    baseInfoTemp.Contact = contact;
                    baseInfoTemp.Mobile = mobile;
                    baseInfoTemp.Phone = phone;
                    baseInfoTemp.RegistDate = registDate;
                    baseInfoTemp.InsuranceCount = insuranceCount;
                    baseInfoTemp.Admin = admin;
                    baseInfoTemp.Remark = remark;

                    baseInfoTemp.PayerName = payerName;
                    baseInfoTemp.IdentityCardNo = identityCardNo;

                    baseInfoTemp.DeptName = currentBigDept.Name;
                    baseInfoTemp.DeptID = currentBigDept.ID;
                    #endregion

                    baseInfoTemp.OperatorID = SystemContext.Current.LoginUser.ID;

                    //添加到list中
                    infoList.Add(baseInfoTemp);
                }
                catch
                {
                    errorMessage += "第" + (i+2) + "行数据存在错误，请检查！<br/>";
                }
            }

            //校验无误，开始保存操作
            if (string.IsNullOrEmpty(errorMessage))
            {
                try
                {
                    using (var db = new DMSEntities())
                    {
                        //执行保存操作
                        foreach (var amBaseInfoTemp in infoList)
                        {
                            ////告警级别计算
                            //CalculateAlarmLevel(amBaseInfoTemp);
                            db.AM_BaseInfoTemp.AddObject(amBaseInfoTemp);
                        }
                        db.SaveChanges();
                        //执行存过，计算告警级别
                        CalculateAlarmLevel(batchId);
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.LogException(ex);
                    errorMessage = ex.Message;
                }
            }

            return errorMessage;
        }

        public static void RemoveAllTempRecord()
        {
            string deleteRecord = "delete from dbo.AM_BaseInfoTemp where [OperatorID] = '{0}'";
            string sqlParam = string.Format(deleteRecord, SystemContext.Current.LoginUser.ID); //传入查询需要检查的sql做参数
            Database db = DBHelper.Instance;
            using (SqlConnection con = (SqlConnection) db.CreateConnection())
            {
                con.Open();
                SqlCommand command = new SqlCommand(sqlParam, con);
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();
            }

        }

        public static void CalculateAlarmLevel(string batchId)
        {
            string sqlParam = string.Format(BaseInfoSql,batchId);//传入查询需要检查的sql做参数
            Database db = DBHelper.Instance;
            using (SqlConnection con = (SqlConnection)db.CreateConnection())
            {
                con.Open();
                SqlCommand command = new SqlCommand("Calculate_AlarmLevel", con);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@sqlParam", SqlDbType.VarChar, 1000);
                command.Parameters["@sqlParam"].Value = sqlParam;
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 解析并设置参保类型
        /// </summary>
        /// <param name="baseInfoTemp"></param>
        /// <param name="insuranceTypesString"></param>
        /// <returns></returns>
        protected static bool GetInsuranceTypes(AM_BaseInfoTemp baseInfoTemp,string insuranceTypesString)
        {
            string[] types = {"养老","工伤","失业","医疗","生育"};
            string[] insuranceTypes = insuranceTypesString.Split('、');
            //参保类型不符合预设的五种类型，返回false
            foreach (var insuranceType in insuranceTypes)
            {
                if (!string.IsNullOrEmpty(insuranceType))
                {
                    if (!types.Contains(insuranceType))
                    {
                        return false;
                    }
                }
            }

            baseInfoTemp.Yanglao = false;
            baseInfoTemp.Gongshang = false;
            baseInfoTemp.Shiye = false;
            baseInfoTemp.Yiliao = false;
            baseInfoTemp.Shengyu = false;

            //符合格式
            foreach(var iType in insuranceTypes)
            {
                switch (iType)
                {
                    case "养老":
                        baseInfoTemp.Yanglao = true;
                        break;
                    case "工伤":
                        baseInfoTemp.Gongshang = true;
                        break;
                    case "失业":
                        baseInfoTemp.Shiye = true;
                        break;
                    case "医疗":
                        baseInfoTemp.Yiliao = true;
                        break;
                    case "生育":
                        baseInfoTemp.Shengyu = true;
                        break;
                }
            }
            return true;
        }
        
        public static int GetCount(string type,string batchId)
        {
            int count = 0;

            using (var db = new DMSEntities())
            {
                if(type=="Error")
                {
                    count = db.AM_BaseInfoTemp.Where(p => p.BatchID == batchId && p.AlarmLevel=="1" && p.IsRemoved==false).Count();
                }else if(type=="Warning")
                {
                    count =
                        db.AM_BaseInfoTemp.Where(
                            p => p.BatchID == batchId && p.AlarmLevel.Contains("2") && p.IsRemoved == false).Count();
                }
            }
            return count;
        }

        public static void CopyTempToBaseInfo(string batchId)
        {
            using (var db = new DMSEntities())
            {
                var tempRecords =
                    db.AM_BaseInfoTemp.Where(p => p.BatchID == batchId && p.IsRemoved == false).ToList();
                List<AM_BaseInfo> infoList = new List<AM_BaseInfo>();
                foreach (var record in tempRecords)
                {
                    var baseInfo = new AM_BaseInfo();
                    List<string> excludeName = new List<string>();
                    excludeName.Add("EntityKey");
                    BeanUtils.Copy(baseInfo, record, typeof (AM_BaseInfoTemp), excludeName);
                    baseInfo.IsDeleted = false;
                    infoList.Add(baseInfo);
                    record.IsRemoved = true;
                }
                db.SaveChanges();
                BaseInfoService.SaveBaseInfoList(infoList);
            }
        }
    }
}
